﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;
using You2V.Data.Shared;
using You2V.LocalDBLayer;
using You2V.Data;
using System.Data;

namespace You2V.LocalDBLayer
{
    public class StudentDBL
    {
        public StudentDBL()
        {
        }

        // chg 20120825, 为了解决冲突，不再使用static
        //public static StudentDBL Instance = new StudentDBL();

		#region student2012 Action 操作新学生表格

        /// <summary>
        /// 搜索学生进行跟踪-只取后台审核通过的学生
        /// </summary>
        /// <param name="keyWord"></param>
        /// <returns></returns>
		public List<Student> GetStudentListByKeyword(string keyWord)
		{
            string strWhere = " 1=1 ";
            if (!string.IsNullOrWhiteSpace(keyWord))
            {
                strWhere = " StudentNumber like '%" + keyWord + "%' OR FullName like '%" + keyWord + "%' OR NickName like '%" + keyWord + "%' ";
            }

            return GetStudentListGiventWhereCondition(strWhere);
		}

        /// <summary>
        /// 搜索学生进行跟踪-只取后台审核通过的学生
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
		public List<Student> GetStudentListGiventWhereCondition(string strWhere)
		{
			List<Student> StudentList = new List<Student>();
			DataTable dt = GetStudentDataTableWithWhereCondition(strWhere);

			if (dt == null)
			{
				return null;
			}

			foreach (DataRow row in dt.Rows)
			{
				Student item = new Student();

				try
				{
					item.tc_id = (int)row["tc_id"]; //todo, 可能为空
				}
				catch (Exception)
				{
					item.tc_id = 0;
				}

                item.Student2012ID = (Guid)row["ID"];
				item.StudentNumber = (string)row["StudentNumber"];
				item.FullName = (string)row["FullName"];
				item.Gender = (string)row["Gender"];
				item.SchoolShortName = (string)row["SchoolShortName"];
				item.ClassName = (string)row["ClassName"];

				StudentList.Add(item);
			}

			return StudentList;
		}

		public DataTable GetStudentDataTableWithWhereCondition(string strWhere)
		{
			//UploadStatus = 2 只取审核通过的学生，TODO:需要再看有没有资助人
			if (strWhere != string.Empty && strWhere != "")
				strWhere = string.Format("AND ({0})", strWhere);
			string sqlQuery = "SELECT ID, tc_id,StudentNumber,FullName,Gender,SchoolShortName,ClassName FROM Student2012 WHERE UploadStatus <> -1 " + strWhere;
			DataTable dt = DBAccess.GetDataTable(sqlQuery);
			return dt;
		}

        private string ReadString(DataRow row, string ColumnName)
        {
            if (row[ColumnName].GetType() == typeof(System.DBNull))
                return string.Empty;

            return (string)row[ColumnName];
        }

		public StudentInfo2012 GetStudentInfo(DataRow row)
        {
            StudentInfo2012 item = new StudentInfo2012();

			if (row["ID"].GetType() != typeof(System.DBNull))
				item.ID = (Guid)row["ID"];
			else
			{
				if (row["tc_id"].GetType() != typeof(System.DBNull))
				{
					item.ID = UpdateStudentID((int)row["tc_id"]);
				}
				else
					item.ID = Guid.Empty; //此时必须保留Empty, 否则保存修改时候会以ID为基础update，就没有更新成功了
			}

            string areaid = (string)row["tc_area"];   //todo, get name from areaid?
            item.StudentArea = GetAreaFromID(areaid);

            item.StudentNumber = (string)row["StudentNumber"];
            item.FullName = (string)row["FullName"];
            item.NickName = (string)row["NickName"];
            item.Gender = (string)row["Gender"];

            try
            {
                item.BirthDate = (DateTime)row["BirthDate"];
            }
            catch
            {
                item.BirthDate = DateTime.MinValue;
            }

            item.Nation = (string)row["Nation"];
            item.SchoolInfo.SchoolShortName = (string)row["SchoolShortName"];
            item.SchoolInfo.SchoolFullName = (string)row["SchoolFullName"];
            item.ClassName = (string)row["ClassName"];
            item.StudentAddress.PostCode = (string)row["StudentAddressPostCode"];
            item.StudentAddress.AddressText = (string)row["StudentAddressText"];

            //todo FamilyMemberList

            item.HomeAddressPostCode = (string)row["HomeAddressPostCode"];
            item.HomeDetailAddressText = (string)row["HomeDetailAddressText"];
            item.HomeAddressTextForPublish = (string)row["HomeAddressTextForPublish"];
            item.HomeIncomeSummary = (string)row["HomeIncomeSummary"];
            item.HouseStatus = (string)row["HouseStatus"];
            item.AssetStatus = (string)row["AssetStatus"];
            item.SponsorSuggestion = (string)row["SponsorSuggestion"];
            item.RewardsJustGain = (string)row["RewardsJustGain"];
            item.LiveInSchoolString = (string)row["LiveInSchool"];
            item.Volunteer = (string)row["Volunteer"];
            item.VolunteerReport = (string)row["VolunteerReport"];
            item.SelfIntroduction = (string)row["SelfIntroduction"];
            item.TeacherFeedback = (string)row["TeacherFeedback"];
            item.Picture1Title = ReadString(row, "Picture1Title");
            item.Picture1URL_2012 = ReadString(row, "Picture1URL_2012");
            item.Picture1URL_v1 = ReadString(row, "Picture1URL_v1");
            item.Picture2Title = ReadString(row, "Picture2Title");
            item.Picture2URL_2012 = ReadString(row, "Picture2URL_2012");
            item.Picture2URL_v1 = ReadString(row, "Picture2URL_v1");
            item.UploadStatus = (int)row["UploadStatus"];
            item.SponsorStatus = (int)row["SponsorStatus"];
            item.SponsorShipTracking = (string)row["SponsorShipTracking"];

            return item;
        }

        public List<StudentPeriod> GetStudentPeriod(int studentID)
        {
            List<StudentPeriod> studentPeriodList = new List<StudentPeriod>();
            string sqlQuery = string.Format("SELECT stu_spon_action.ID, stu_spon_link.stu_ID, stu_spon_link.spon_ID, "
                + " stu_spon_action.stuinfo_ID, stu_spon_action.period_ID, stu_spon_action.action_ID, stu_spon_action.action_date, "
                + " stu_spon_action.amount, stu_spon_action.description"
                + " FROM stu_spon_link LEFT OUTER JOIN stu_spon_action "
                + " ON stu_spon_link.ID = stu_spon_action.link_ID AND stu_spon_link.stu_ID = {0};", studentID);

            DataTable dt = DBAccess.GetDataTable(sqlQuery);

            if (dt == null)
            {
                return null;
            }

            foreach (DataRow row in dt.Rows)
            {
                studentPeriodList.Add(null);
            }

            return studentPeriodList;
        }

        public List<SchoolInfo> GetSchoolListByArea(string area)
        {
            List<SchoolInfo> SchoolList = new List<SchoolInfo>();

            string strQuery = " SELECT SchoolShortName, SchoolFullName FROM You2vSchoolList WHERE tc_area = '" + area + "' ORDER BY SchoolShortName ";
            DataTable dt = DBAccess.GetDataTable(strQuery);

            if (dt == null)
            {
                return null;
            }

            foreach (DataRow row in dt.Rows)
            {
                SchoolInfo item = new SchoolInfo();

                item.SchoolShortName = (string)row["SchoolShortName"];
                item.SchoolFullName = (string)row["SchoolFullName"];

                SchoolList.Add(item);
            }

            return SchoolList;
        }

        public StudentInfo2012 GetStudent2012ByID(Guid ID)
        {
            string sqlQuery = "SELECT * FROM Student2012 WHERE ID = {" + ID + "}";
            
            DataTable dt = DBAccess.GetDataTable(sqlQuery);

            if (dt == null)
            {
                return null;
            }

            StudentInfo2012 item = new StudentInfo2012();
            bool found = false;
            foreach (DataRow row in dt.Rows)
            {
                found = true;

                item = GetStudentInfo(row);

            }

            if (!found)
            {
                Debug.WriteLine("Cann't find student2012 info for ID=" + ID);
            }

            // 读取FamilyMemberList
            if (item.ID != Guid.Empty)
            {
                //GUID的where 必须用{ }
                sqlQuery = "SELECT * FROM StudentFamilyMembers WHERE StudentID = {" + item.ID + "} order by SequenceNumber";

                dt = DBAccess.GetDataTable(sqlQuery);

                if (dt == null)
                {
                    return null;
                }

                foreach (DataRow row in dt.Rows)
                {
                    FamilyMember member = new FamilyMember();
                    member.ID = (Guid)row["ID"];
                    member.RelationShip = (string)row["RelationShip"];
                    member.FullName = (string)row["FullName"];
                    member.Age = (string)row["Age"];
                    member.Job = (string)row["Job"];
                    member.HealthStatus = (string)row["HealthStatus"];
                    member.JobOutOfHometown = (string)row["JobOutOfHometown"];

                    item.FamilyMemberList.Add(member);
                }
            }

            return item;
        }

		public Guid UpdateStudentID(int tc_id)
		{
			Guid p_newid = Guid.NewGuid();
			string sqlQuery = string.Format("select ID from student2012 where tc_id = {0}", tc_id);
			DataTable dt = DBAccess.GetDataTable(sqlQuery);

			if (dt == null || dt.Rows.Count != 1)
            { throw new ObjectNotFoundException("无法获得学生数据，请刷新重试"); }


			if (dt.Rows[0]["ID"].GetType() != typeof(System.DBNull))
			{
				return (Guid)dt.Rows[0]["ID"];
			}
			else
			{
				sqlQuery = string.Format("UPDATE student2012 SET ID = '{0}' WHERE tc_id = {1}", p_newid, tc_id);
				DBAccess.RunCommand(sqlQuery);
			}

			return p_newid;
		}

        /// <summary>
        /// 读取学生列表
        /// 只取得少量信息
        /// </summary>
        /// <returns></returns>
        public List<StudentInfo2012> GetStudent2012List(string keyWord)
        {
            List<StudentInfo2012> Student2012List = new List<StudentInfo2012>();

            string strWhere = string.Empty;
            if (!string.IsNullOrWhiteSpace(keyWord))
            {
                strWhere = " WHERE StudentNumber like '%" + keyWord + "%' OR FullName like '%" + keyWord + "%' OR NickName like '%" + keyWord + "%' ";
            }

            string sqlQuery = "SELECT ID, StudentNumber,FullName,Gender,SchoolShortName,ClassName FROM Student2012 " + strWhere + " ORDER BY StudentNumber";
            DataTable dt = DBAccess.GetDataTable(sqlQuery);

            if (dt == null)
            {
                return null;
            }

            foreach (DataRow row in dt.Rows)
            {
                StudentInfo2012 item = new StudentInfo2012();

                item.ID = (Guid)row["ID"];
                item.StudentNumber = (string)row["StudentNumber"];
                item.FullName = (string)row["FullName"];
                item.Gender = (string)row["Gender"];
                item.SchoolInfo.SchoolShortName = (string)row["SchoolShortName"];
                item.ClassName = (string)row["ClassName"];
                Student2012List.Add(item);

            }

            return Student2012List;
        }

		public bool SaveNewStudent2012(StudentInfo2012 Student)
		{
			if (Student == null)
			{
				return false;
			}

			string sqlCommand = " INSERT INTO Student2012 \r\n"
								+ "(ID,tc_area,StudentNumber,FullName,NickName,Gender,BirthDate,LiveInSchool,Nation,SchoolShortName,SchoolFullName,"
								+ "ClassName,StudentAddressPostCode,StudentAddressText,HomeAddressPostCode,HomeDetailAddressText,HomeAddressTextForPublish,"
                                + "HomeIncomeSummary,HouseStatus,AssetStatus,SponsorSuggestion,RewardsJustGain,Volunteer,VolunteerReport,SelfIntroduction,TeacherFeedback,"
                                + "UploadStatus,SponsorStatus,SponsorShipTracking)\r\n"
								+ "VALUES( \r\n"
								+ "'" + Student.ID.ToString() + "',\r\n"
								+ "'" + Student.StudentArea.area + "',\r\n"
								+ "'" + Student.StudentNumber + "',\r\n"
								+ "'" + Student.FullName + "',\r\n"
								+ "'" + Student.NickName + "',\r\n"
								+ "'" + Student.Gender + "',\r\n"
								+ "'" + Student.BirthDate.ToString("MM/dd/yyyy") + "',\r\n" //todo, MDB不支持1/1/0001, 会保存为1/1/2001
								+ "'" + Student.LiveInSchoolString + "',\r\n"
								+ "'" + Student.Nation + "',\r\n"
								+ "'" + Student.SchoolInfo.SchoolShortName + "',\r\n"
								+ "'" + Student.SchoolInfo.SchoolFullName + "',\r\n"
								+ "'" + Student.ClassName + "',\r\n"
								+ "'" + Student.StudentAddress.PostCode + "',\r\n"
								+ "'" + Student.StudentAddress.AddressText + "',\r\n"
								+ "'" + Student.HomeAddressPostCode + "',\r\n"
								+ "'" + Student.HomeDetailAddressText + "',\r\n"
								+ "'" + Student.HomeAddressTextForPublish + "',\r\n"
								+ "'" + Student.HomeIncomeSummary + "',\r\n"
								+ "'" + Student.HouseStatus + "',\r\n"
								+ "'" + Student.AssetStatus + "',\r\n"
								+ "'" + Student.SponsorSuggestion + "',\r\n"
								+ "'" + Student.RewardsJustGain + "',\r\n"
								+ "'" + Student.Volunteer + "',\r\n"
								+ "'" + Student.VolunteerReport + "',\r\n"
								+ "'" + Student.SelfIntroduction + "',\r\n"
								+ "'" + Student.TeacherFeedback + "',\r\n"
                                + "-1, \r\n"
                                + "0, \r\n"
                                + "'未开始资助' \r\n"
								+ ") \r\n";

			bool succ = DBAccess.RunCommand(sqlCommand);

            if (succ)
            {
                //保存family member
                for (int i = 0; i < Student.FamilyMemberList.Count; i++)
                {
                    FamilyMember member = Student.FamilyMemberList[i];

                    sqlCommand = " INSERT INTO StudentFamilyMembers \r\n"
                                + " (ID, StudentID, SequenceNumber, RelationShip, FullName, Age, Job, HealthStatus, JobOutofHometown) \r\n"
                                + " VALUES ( \r\n"
                                + "'" + member.ID.ToString() + "',\r\n"
                                + "'" + Student.ID.ToString() + "',\r\n"
                                + i + ",\r\n"
                                + "'" + member.RelationShip + "',\r\n"
                                + "'" + member.FullName + "',\r\n"
                                + "'" + member.Age + "',\r\n"
                                + "'" + member.Job + "',\r\n"
                                + "'" + member.HealthStatus + "',\r\n"
                                + "'" + member.JobOutOfHometown + "'\r\n"
                                + ") \r\n";

                    //Debug.WriteLine(sqlCommand);
                    bool ret = DBAccess.RunCommand(sqlCommand);
                    if (!ret)
                        return false;
                }

                return true;
            }
            else
            {
                return false;
            }
		}

		public bool UpdateStudent2012(StudentInfo2012 Student)
		{
			string sqlCommand = string.Empty;

            //根据最新格式, GUID肯定不为空
			//用GUID来更新
			// 就算tc_id有数，也不变，所以update语句不用管它
			sqlCommand = "Update Student2012 \r\n"
							+ "SET FullName = '" + Student.FullName + "', \r\n"
							+ "tc_area = '" + Student.StudentArea.area + "', \r\n"
							+ "NickName = '" + Student.NickName + "', \r\n"
							+ "Gender = '" + Student.Gender + "', \r\n"
							+ "BirthDate = '" + Student.BirthDate.ToString("MM/dd/yyyy") + "', \r\n"     //todo, MDB不支持1/1/0001, 会保存为1/1/2001
							+ "Nation = '" + Student.Nation + "', \r\n"
							+ "SchoolShortName = '" + Student.SchoolInfo.SchoolShortName + "', \r\n"
							+ "SchoolFullName = '" + Student.SchoolInfo.SchoolFullName + "', \r\n"
							+ "ClassName = '" + Student.ClassName + "', \r\n"
							+ "StudentAddressPostCode = '" + Student.StudentAddress.PostCode + "', \r\n"
							+ "StudentAddressText = '" + Student.StudentAddress.AddressText + "', \r\n"
							+ "HomeAddressPostCode = '" + Student.HomeAddressPostCode + "', \r\n"
							+ "HomeDetailAddressText = '" + Student.HomeDetailAddressText + "', \r\n"
							+ "HomeAddressTextForPublish = '" + Student.HomeAddressTextForPublish + "', \r\n"
							+ "HomeIncomeSummary = '" + Student.HomeIncomeSummary + "', \r\n"
							+ "HouseStatus = '" + Student.HouseStatus + "', \r\n"
							+ "AssetStatus = '" + Student.AssetStatus + "', \r\n"
							+ "SponsorSuggestion = '" + Student.SponsorSuggestion + "', \r\n"
							+ "RewardsJustGain = '" + Student.RewardsJustGain + "', \r\n"
							+ "LiveInSchool = '" + Student.LiveInSchoolString + "', \r\n"
							+ "Volunteer = '" + Student.Volunteer + "', \r\n"
							+ "VolunteerReport = '" + Student.VolunteerReport + "', \r\n"
							+ "SelfIntroduction = '" + Student.SelfIntroduction + "', \r\n"
							+ "TeacherFeedback = '" + Student.TeacherFeedback + "' \r\n"
							+ "WHERE ID= {" + Student.ID + "}\r\n";

			bool succ = DBAccess.RunCommand(sqlCommand);

            if (succ)
            {
                //家庭成员，先删除再重新插入
                sqlCommand = " DELETE FROM StudentFamilyMembers WHERE StudentID = {" + Student.ID.ToString() + "}";
                DBAccess.RunCommand(sqlCommand);    //todo, 判断是否成功

                for (int i = 0; i < Student.FamilyMemberList.Count; i++)
                {
                    FamilyMember member = Student.FamilyMemberList[i];

                    sqlCommand = " INSERT INTO StudentFamilyMembers \r\n"
                                + " (ID, StudentID, SequenceNumber, RElationShip, FullName, Age, Job, HealthStatus, JobOutofHometown) \r\n"
                                + " VALUES ( \r\n"
                                + "'" + member.ID.ToString() + "',\r\n"
                                + "'" + Student.ID.ToString() + "',\r\n"
                                + i + ",\r\n"
                                + "'" + member.RelationShip + "',\r\n"
                                + "'" + member.FullName + "',\r\n"
                                + "'" + member.Age + "',\r\n"
                                + "'" + member.Job + "',\r\n"
                                + "'" + member.HealthStatus + "',\r\n"
                                + "'" + member.JobOutOfHometown + "'\r\n"
                                + ") \r\n";

                    //Debug.WriteLine(sqlCommand);
                    if (!DBAccess.RunCommand(sqlCommand))
                        return false;
                }

                return true;
            }
            else
            {
                return false;
            }
		}
		#endregion

		#region student enducation Action 操作学生最新联系情况表格

		public StudentEducationInfo GetAvaliableStudentEduInfo(Guid student2012ID)
		{
			List<StudentEducationInfo> studentInfoList = GetStudentAllEduInfo(student2012ID, 1);
			if (studentInfoList != null && studentInfoList.Count > 0)
				return studentInfoList[0];
			return null;
		}

		/// <summary>
		/// Get Student Edu Info from old table
		/// </summary>
		/// <param name="student2012ID">stu_id in old table</param>
		/// <param name="status">if -1, then return all</param>
		/// <returns>Student Education Info Table</returns>
		public List<StudentEducationInfo> GetStudentAllEduInfo(Guid student2012ID, int status)
		{
			List<StudentEducationInfo> studentInfoList = new List<StudentEducationInfo>();
			string statusinfo = "";
			if (status != -1)
				statusinfo = string.Format(" AND status = {0}", status);
			string sqlQuery = string.Format("SELECT * FROM stu_edu_info WHERE stu2012_ID = {{{0}}}{1}", student2012ID, statusinfo);

			DataTable dt = DBAccess.GetDataTable(sqlQuery);

            if (dt == null || dt.Rows.Count == 0)
            {
                studentInfoList.Add(InsertAndGetDefaultStudentEduInfo(student2012ID));
            }
            else
            {
                foreach (DataRow row in dt.Rows)
                {
                    studentInfoList.Add(GetStudentEduInfo(row));
                }
            }

			return studentInfoList;
		}
		public StudentEducationInfo GetStudentEduInfo(Guid studentEduInfoID)
		{
			string sqlQuery = string.Format("SELECT * FROM stu_edu_info WHERE ID = {{{0}}}", studentEduInfoID);
			DataTable dt = DBAccess.GetDataTable(sqlQuery);

			if (dt != null && dt.Rows.Count == 1)
			{
				return GetStudentEduInfo(dt.Rows[0]);
			}

            throw new ObjectNotFoundException("无法获得学生数据，请刷新重试");
		}
		public StudentEducationInfo GetStudentEduInfo(DataRow studentInfoRow)
		{
			StudentEducationInfo item = new StudentEducationInfo();
			item.tceduinfo_id = (Guid)studentInfoRow["ID"];
			item.tc2012_id = (Guid)studentInfoRow["stu2012_ID"];
			item.schoolName = (string)studentInfoRow["school"];
			item.className = (string)studentInfoRow["class"];
			item.amount = (string)studentInfoRow["amount"];
			item.volunteer = (string)studentInfoRow["volunteer"];
			item.teacher = (string)studentInfoRow["teacher"];
			item.contact = (string)studentInfoRow["contact"];
			if ((int)studentInfoRow["status"] == 1)
				item.enabled = true;
			else
				item.enabled = false;
			item.CreatedBy = (string)studentInfoRow["create_by"];
			item.CreatedDate = (DateTime)studentInfoRow["create_date"];
			return item;
		}

        public StudentEducationInfo InsertAndGetDefaultStudentEduInfo(Guid student2012ID)
        {
            // todo, reuse code with InsertDefaultStudentEduInfo

            Guid edu_ID = Guid.NewGuid();
            string sqlQuery = string.Format(
                "INSERT INTO stu_edu_info "
                + "(ID, stu2012_ID, school, class, amount, volunteer, teacher, contact, status, create_by, create_date) "
                + "SELECT "
                + "'{0}' as ID, "
                + "ID as stu2012_ID, "
                + "SchoolFullName as school,  "
                + "ClassName as class, "
                + "SponsorSuggestion as amount, "
                + "Volunteer, "
                + "'待添加' as teacher, "
                + "'待添加' as contact, "
                + "1 as status, "
                + "'{1}' as create_by, "
                + "'{2}' as create_date "
                + " FROM Student2012 WHERE ID = {{{3}}}"
                , edu_ID, You2VDataDictionary.currentUser, DateTime.Now.ToString(), student2012ID);

            //修改所谓延迟问题，GetDataTable是查询结果用的，RunCommand才是执行命令更新用的
            //DBAccess.GetDataTable(sqlQuery);
            if (!DBAccess.RunCommand(sqlQuery))
            {
                Debug.WriteLine("SQL Error 39492");
                return null;
            }

            //System.Threading.Thread.Sleep(100);
            return GetStudentEduInfo(edu_ID);

        }

		public bool InsertDefaultStudentEduInfo(Guid student2012ID)
		{
			Guid edu_ID = Guid.NewGuid();
			string sqlQuery = string.Format(
				"INSERT INTO stu_edu_info "
				+ "(ID, stu2012_ID, school, class, amount, volunteer, teacher, contact, status, create_by, create_date) "
				+ "SELECT "
				+		"'{0}' as ID, "
				+		"ID as stu2012_ID, "
				+		"SchoolFullName as school,  "
				+		"ClassName as class, "
				+		"SponsorSuggestion as amount, "
				+		"Volunteer, "
				+		"'待添加' as teacher, "
				+		"'待添加' as contact, "
				+		"1 as status, "
				+		"'{1}' as create_by, "
				+		"'{2}' as create_date "
				+ " FROM Student2012 WHERE ID = {{{3}}}"
				, edu_ID, You2VDataDictionary.currentUser, DateTime.Now.ToString(), student2012ID);

            //修改所谓延迟问题，GetDataTable是查询结果用的，RunCommand才是执行命令更新用的
            //DBAccess.GetDataTable(sqlQuery);
            if (!DBAccess.RunCommand(sqlQuery))
            {
                Debug.WriteLine("SQL Error 58392");
                return false;
            }

            return true;

		}
		public StudentEducationInfo InsertNewStudentEduInfo(Guid student2012ID, string school, string className, string amount, string volunteer, string teacher, string contact)
		{
			MarkAllEduInfoExpired(student2012ID);

			Guid edu_ID = Guid.NewGuid();
			string sqlQuery = string.Format(
				"INSERT INTO stu_edu_info "
				+ "(ID, stu2012_ID, school, class, amount, volunteer, teacher, contact, status, create_by, create_date) "
				+ "VALUES "
				+ "({{{0}}}, {{{1}}}, '{2}', '{3}','{4}','{5}','{6}','{7}',1,'{8}','{9}')"
				, edu_ID, student2012ID, school, className, amount, volunteer, teacher, contact, You2VDataDictionary.currentUser, DateTime.Now.ToString());

			DBAccess.RunCommand(sqlQuery);

			//System.Threading.Thread.Sleep(100);
			return GetStudentEduInfo(edu_ID);
		}
		public void MarkAllEduInfoExpired(Guid student2012ID)
		{
			string sqlQuery = string.Format(
				"UPDATE stu_edu_info SET status = 0 WHERE stu2012_ID = {{{0}}}"
				, student2012ID);
			DBAccess.RunCommand(sqlQuery);
		}

		#endregion

		#region Student period action 操作学生资助情况表格
		public StudentPeriodCollection GetStudentPeriod(Guid Student2012ID)
		{
			StudentPeriodCollection p_result = new StudentPeriodCollection();
			string sqlQuery = string.Format("SELECT a.* FROM stu_period_action a INNER JOIN stu_edu_info b ON a.EduInfo_ID = b.ID WHERE b.stu2012_ID = {{{0}}} ORDER BY a.ActionDate, a.Action", Student2012ID);
			DataTable dt = DBAccess.GetDataTable(sqlQuery);

            if (dt != null)
            {
                foreach (DataRow p_r in dt.Rows)
                {
                    string _periodName = (string)p_r["Period"];
                    p_result.Period(_periodName).AddStuPeriodAction(GetStudentPeriodAction(p_r));
                }
                List<string> _enablePeriod = GetAvaliablePeriod();
                foreach (string p_str in _enablePeriod)
                {
                    p_result.Period(p_str).AddStuPeriodAction(null);
                }
            }
            else
            {
                throw new ObjectNotFoundException("无法获得学生数据，请刷新重试");
            }

			return p_result;
		}

		public DataTable GetStudentPeriodAction(string Period, Guid StudentID)
		{
			string sqlQuery 
				= string.Format(
				"SELECT a.Action, a.Amount, a.ActionDate FROM stu_period_action a INNER JOIN stu_edu_info b ON a.EduInfo_ID = b.ID WHERE b.stu2012_ID = {{{0}}} and a.Period = '{1}' and a.Status = 1"
					, StudentID, Period);
			DataTable dt = DBAccess.GetDataTable(sqlQuery);
			return dt;
		}

		public StudentPeriodAction GetStudentPeriodAction(Guid PeriodActionID)
		{
			string sqlQuery = string.Format("SELECT * FROM stu_period_action WHERE ID = {{{0}}}", PeriodActionID);
			DataTable dt = DBAccess.GetDataTable(sqlQuery);

			if (dt != null && dt.Rows.Count == 1)
			{
				return GetStudentPeriodAction(dt.Rows[0]);
			}

            throw new ObjectNotFoundException("无法获得学生数据，请刷新重试");
		}

		public StudentPeriodAction GetStudentPeriodAction(DataRow row)
		{
			StudentPeriodAction item = new StudentPeriodAction();
			item.ID = (Guid)row["ID"];
			item.edu_info = GetStudentEduInfo((Guid)row["EduInfo_ID"]);
			item.action = (string)row["action"];
			item.amount = (int)row["amount"];
			item.actionDate = (DateTime)row["ActionDate"];
			item.description = (string)row["Description"];
			item.delDesc = (string)row["delDesc"];
			item.Period = (string)row["Period"];
			if ((int)row["status"] == 1)
				item.enabled = true;
			else
				item.enabled = false;
			
			item.CreatedBy = (string)row["created_by"];
			item.CreateTime = (DateTime)row["created_date"];
			item.MoidifiedBy = (string)row["modified_by"];
			item.ModifiedTime = (DateTime)row["modified_date"];
			return item;
		}

		public StudentPeriodAction InserNewStudentPeriodAction(StudentPeriodAction item)
		{
			Guid _stuPeriodActionID = Guid.NewGuid();
			string sqlQuery = string.Format(
				"INSERT INTO stu_period_action "
				+ "([ID], [EduInfo_ID], [Action], [Amount], [Description], [Status], created_by, created_date, modified_By, modified_Date, [Period], [actionDate], [delDesc]) "
				+ "Values ({{{0}}},{{{1}}},'{2}',{3},'{4}',1, '{5}','{6}','{5}','{6}','{7}','{8}',' ')"
				, _stuPeriodActionID, item.edu_info.tceduinfo_id, item.action, item.amount, item.description, You2VDataDictionary.currentUser
				, DateTime.Now.ToString(), item.Period, item.actionDate);

			DBAccess.RunCommand(sqlQuery);
			return GetStudentPeriodAction(_stuPeriodActionID);
		}

		public StudentPeriodAction DisableStudentPeriodAction(Guid ID, string Description)
		{
			string sqlQuery = string.Format(
				"UPDATE stu_period_action SET [Status]=0, [delDesc]='{0}', [modified_Date]='{1}', [modified_By]='{2}'"
				+ " WHERE [ID] = {{{3}}}"
				, Description, DateTime.Now.ToString(), You2VDataDictionary.currentUser, ID);

			DBAccess.RunCommand(sqlQuery);
			return GetStudentPeriodAction(ID);
		}
		#endregion

		public Area GetAreaFromID(string areaid)
        {
            if (You2VDataDictionary.AreaList != null)
            {
                foreach (Area Area in You2VDataDictionary.AreaList)
                {
                    if (Area.area.Equals(areaid))
                    {
                        return Area;
                    }
                }
            }
            return null;
        }

		public List<string> GetAvaliablePeriod()
		{
			List<string> p_result = new List<string>();

			string sqlQuery = "SELECT period_name from setting_period where status in (1,2)";
			DataTable tb = DBAccess.GetDataTable(sqlQuery);
			if (tb != null)
			{
				foreach (DataRow p_r in tb.Rows)
				{
					p_result.Add((string)p_r["period_name"]);
				}
			}
			return p_result;
		}

		public List<string> GetCurrentPeriod()
		{
			List<string> p_result = new List<string>();

			string sqlQuery = "SELECT period_name from setting_period where status in (1,2) ";
			DataTable tb = DBAccess.GetDataTable(sqlQuery);
			if (tb != null)
			{
				foreach (DataRow p_r in tb.Rows)
				{
					p_result.Add((string)p_r["period_name"]);
				}
			}
			return p_result;
		}

		public DataTable GetStudentReport()
		{
			string sqlQuery = "SELECT a.ID, a.StudentNumber, a.FullName, "
				+ "b.school, b.class, b.amount, b.teacher, b.contact, "
				+ "(im.tc_nam + '(' + im.tc_call + ')') as sponsor, "
				+ "im.tc_mail as spon_mail, im.tc_tel as spon_tel, (im.tc_dz + ' ' + im.tc_post) as spon_Address "
				+ "FROM (Student2012 a LEFT JOIN stu_edu_info b on a.ID = b.stu2012_ID) "
                + " INNER JOIN im ON a.tc_id = im.tc_studentid WHERE b.status = 1";
			DataTable tb = DBAccess.GetDataTable(sqlQuery);
			return tb;
		}

		
    }
}
